﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using CustomControlDatabaseInterface.Utilities;

namespace CustomControlDatabaseInterface.Model
{


    /// <summary>
    /// The Purpose of this Class is to maintain Information about all Customers, such as a 
    /// Collection of CustomerNames, the Number of Customers and such. 
    /// It also contains Members to provide filtered Information about all Customers. 
    /// </summary>
    public class AllCustomersModel
    {

        public List<String> AllCustomers { get; private set; }
        public int CustomerCount { get; private set; } 


        public AllCustomersModel()
        {
            AllCustomers = RefreshAllCustomers(); 
            CustomerCount = AllCustomers.Count; 
        }


        public List<String> FilterbyName(String name)
        {
            String pattern = "^"+name;
            List<String> result = AllCustomers.Where(x => Regex.IsMatch(x, pattern, RegexOptions.IgnoreCase) == true).ToList();
            return result; 
        }

        public List<String> FilterByTechnology(String technologyName)
        {
            String query = "USE " + DatabaseUtil.Database +";";
            String columnName = "kunde"; 
            query += "SELECT " +columnName+ " FROM " +technologyName + " ORDER BY " +columnName+";";
            List<string> result = DatabaseUtil.GetDataColumn(query, columnName);
            return result; 
        }

        public List<String> FilterByControls(String technologyName, List<String> controls)
        {
            String query = "USE " + DatabaseUtil.Database +";";
            String columnName = "kunde"; 
            query += "Select " +columnName+ " FROM " + technologyName + " WHERE ";
            query += "`" + controls[0] + "`!='' ";
            for (int i = 1; i < controls.Count; i++)
            {
                query += "AND `" + controls[i] + "` !=''";
            }
            query += ";";
            return DatabaseUtil.GetDataColumn(query, columnName); 
        }

        private List<String> RefreshAllCustomers()
        {
            List<string> tableNames = DatabaseUtil.GetTableNames();
            String columnName = "kunde"; 
            String query = "USE " + DatabaseUtil.Database +";";
            if(tableNames.Count < 2)
            {
                query += "SELECT " +columnName+ " FROM " +tableNames[0]+ "ORDER BY " +columnName+ ";";
            }
            else
            {
                query += "SELECT " + columnName + " FROM " + tableNames[0];
                for (int i = 1; i < tableNames.Count; i++ )
                {
                    query += " UNION DISTINCT SELECT " + columnName + " FROM " + tableNames[i];
                }
                query += " ORDER BY " +columnName+";";
            }
            return DatabaseUtil.GetDataColumn(query, columnName); 
        }
        
    }
}
